#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# Author : Mike Tung
# License: MIT License
# File   : mysql.py
# Date   : 2021-06-15 14-09
# Version: 0.1
# Description: connection mysql manager.

from conf.settings import *
import pymysql
from common import logger
log=logger.tlog()
logger=log.define()


class MySQLCommand(object):
    #def __init__(self,host,port,user,passwd,db,table):
    def __init__(self,dbinfo):
        self.host = dbinfo['HOST']
        self.port = dbinfo['PORT']
        self.user = dbinfo['USER']
        self.password = dbinfo['PASSWORD']
        self.db = dbinfo['DATABASE']
        self.table = dbinfo['TABLE']

    def connectMysql(self):
        try:
            #logger.info(self.port)
            self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.password,db=self.db,charset='utf8')
            self.cursor = self.conn.cursor()
            #logger.info("connect sucess!")
        except:
            logger.info('connect mysql error.')
            
    def executeMysql(self,sql):
        try:
            #logger.info(sql)
            self.conn.ping()
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            logger.error('SQLExecuteFailed:' + sql)
            logger.error(e)
    
    def executeBuleMysql(self,sql,list):
        try:
            self.conn.ping()
            self.cursor.execute(sql,list)
            self.conn.commit()
        except Exception as e:
            logger.error('SQLExecuteFailed:' + sql)
            logger.error(e)
        
    def queryOne(self,sql):
        try:
            self.conn.ping()
            self.cursor.execute(sql)
            row = self.cursor.fetchone()
            # print(row)
            return row
        except:
            logger.info(sql + ' execute failed.')

    def queryMysql(self,sql):
        # sql = "SELECT * FROM " + self.table
        
        try:
            self.conn.ping()
            self.cursor.execute(sql)
            desc = self.cursor.description
            # print(desc)
            rows = self.cursor.fetchall()
            data_dict = [dict(zip([col[0] for col in desc], row)) for row in rows]
            return data_dict
        except:
            logger.info(sql + ' execute failed.')

        

    def insertMysql(self,id,name,sex):
        sql = "INSERT INTO " + self.table + " VALUES(" + id + "," + "'" + name + "'," + "'" + sex + "')"
        
        try:
            self.conn.ping()
            self.cursor.execute(sql)
        except:
            logger.info("insert failed.")
        

    def updateMysqlSN(self,id,name,sex):
        sql = "UPDATE " + self.table + " SET sex='" + sex + "'" + " WHERE name='" + name + "'"
        print("update sn:" + sql)
        try:
            self.conn.ping()
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            self.conn.rollback()
    
    def closeMysql(self):
        self.cursor.close()
        self.conn.close()
               

if __name__ == '__main__':
    x=MySQLCommand(DBConfig['default'])
    x.connectMysql()
    sql ='SELECT * FROM company_key_personnel_info'
    x.queryMysql(sql)
    
    
    